# app.py
from flask import Flask, redirect, render_template, request, jsonify, url_for
from models import db, Student, MysqlOperation
import pymysql as sql

app = Flask(__name__)


app.static_folder = r'E:\Vscode\project_student_management\apps\static'  # 设置静态文件目录
app.static_url_path = '/static'  # 设置静态文件url路径
app.template_folder = r"E:\Vscode\project_student_management\apps\veiw\templates"  # 设置模板文件目录

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)

with app.app_context():
    db.create_all()

sqlopt = MysqlOperation()


def pymysql_connect():
    conn = sql.connect(host="127.0.0.1",port=3306,user="root",password="123456",database="xihu",charset='utf8')
    return conn

@app.route('/')
def index():
    return render_template("login.html")

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        pwd = sqlopt.query_login_userpwd(username)
        if pwd == password:
            # 登录成功，重定向到首页
            return redirect(url_for('jump_to_shouye'))
        else:
            return render_template("error.html", name = "用户名或密码错误！")
    else:
        username = request.args.get('username')
        return render_template("hello.html", username) 
    
@app.route('/shouye')
def jump_to_shouye():
    # name_li = ["id", "name", "grade"]
    students = sqlopt.query_all_students()
    # students = [dict(zip(name_li, student)) for student in students]
    return render_template("student_management.html", students=students)   


@app.route('/addstudent')
def jump_to_add_student():
    return render_template("add_student.html")


# 获取所有学生信息
@app.route('/get_students', methods=['GET'])
def get_students():
    name_li = ["id", "name", "grade"]
    students = sqlopt.query_all_students()
    return jsonify([dict(zip(name_li, student)) for student in students])
       

# 获取单个学生信息
@app.route('/shouye/<int:id>', methods=['GET'])
def get_student(id):
    student = Student.get_student_by_id(id)
    if student:
        return jsonify(student.to_dict())
    else:
        return jsonify({'message': 'Student not found'}), 404

# 添加新学生
@app.route('/add_student', methods=['POST'])
def add_student():
    data = request.get_json()
    student = Student.create_student(data['name'], data['grade'])
    return jsonify(student.to_dict()), 201

# 点击修改页面跳转到学生管理页面


# 更新学生信息
@app.route('/shouye/<int:id>', methods=['PUT'])
def edit_student(id):
    student = Student.get_student_by_id(id)
    if student:
        data = request.get_json()
        student.update_student(data.get('name'), data.get('grade'))
        return jsonify(student.to_dict())
    else:
        return jsonify({'message': 'Student not found'}), 404

# 删除学生
@app.route('/delete_student/<int:id>', methods=['POST'])
def delete_student(id):
    student = sqlopt.get_student_by_id(id)
    if student:
        sqlopt.delete_student_by_id(id)
        return jsonify({'message': True})
    else:
        return jsonify({'message': False}), 404

@app.route("/index1")
def form_test1():
    name = "柯莱特"
    compara_name = [{"name": "移动", "city": "北京"},
                    {"name": "电信", "city": "北京"},
                    {"name": "联通", "city": "北京"},
                    {"name": "中兴", "city": "北京"},
                    {"name": "华为", "city": "北京"},
                    {"name": "小米", "city": "北京"}
                    ]
    return render_template("test.html",name=name,compara_name=compara_name)


@app.route("/compare/<string:name>")
def compare(name):
    info = f"这是一家公司，公司名称叫{name}"
    return render_template("compare.html",name=name,info=info)


# 用ORM中新增供应商数据
@app.route("/compare")
def compare_by_name():
    id = request.args.get('id')
    name = request.args.get('name')
    city = request.args.get('city')
    connection = pymysql_connect()
    with connection.cursor() as cursor:
        #执行SQL语句，占位符被后面元组中的内容替换
        cursor.execute("UPDATE compare SET name='{}', city='{}' where id={};".format(name, city, id))
        connection.commit() # 提交
        connection.close()
    return '修改成功!'


@app.route("/num", methods=["POST"])
def form_test2():
    if request.method == 'POST':
        input_json = request.get_json()
        input3 = input_json["input1"] + input_json["input2"]
        print(input3)
        return jsonify({'res': "提交成功"})
    else:
        return jsonify({'message': "提交失败"})

if __name__ == '__main__':
    app.run(debug=True)